const sql = require('mssql');
const baseFn = require('../../public/base.js');

/**
 * @description 购退审核
 */



// 查询购退单信息
const getVerifyPurchaseRetreatOrder = async (ctx) => {
    try {
        const { index, pageSize, searchList } = ctx.request.body;
        let totalSql = ` SELECT COUNT(TZ.ID) AS NUM FROM THD_ZB TZ LEFT JOIN BM_WLDW DW ON TZ.DWBM = DW.BM WHERE 1=1 `;
        let selectSql = `SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY TZ.ID) AS PX,TZ.RQ,TZ.ID,TZ.DWBM,TZ.DWMC,TZ.BMBM,DW.DH,TZ.JSFSMC,TZ.ZSL,
        TZ.ZJE,TZ.SH,TZ.WLDH,TZ.YFJS,TZ.WLGS,TZ.CLYJ FROM THD_ZB TZ LEFT JOIN BM_WLDW DW ON TZ.DWBM = DW.BM WHERE 1=1 `;
        let publicSql = '';
        if (searchList.isSh) {
            publicSql += ` AND TZ.SH = '${searchList.isSh}' `;
        }
        if (0 in searchList.times) {
            publicSql += ` AND TZ.RQ >= '${searchList.times[0]} 00:00:00' AND TZ.RQ <= '${searchList.times[1]} 23:59:59' `;
        }
        if (searchList.THID) {
            publicSql += ` AND TZ.ID = '${searchList.THID}' `;
        }
        if (searchList.DWBM) {
            publicSql += ` AND (TZ.DWBM LIKE '%${searchList.DWBM}%' OR DW.MC LIKE '%${searchList.DWBM}%' OR DW.JP LIKE '%${searchList.DWBM}%'  `;
        }
        if (searchList.YPBM) {
            publicSql += ` AND TZ.ID IN (SELECT TM.ID FROM THD_MX TM LEFT JOIN BM_YP BP ON TM.YPBM = BP.BM WHERE TM.YPBM LIKE '%${searchList.YPBM}%' OR
        BP.MC LIKE '%${searchList.YPBM}%' OR BP.JP LIKE '%${searchList.YPBM}%' ) `;
        }
        totalSql += publicSql;
        selectSql += publicSql + ` )  T2 WHERE T2.PX between ${(index - 1) * pageSize + 1} and ${index * pageSize} `;

        let totalResult = await sql.query(totalSql);
        let selectResult = await sql.query(selectSql);
        ctx.body = { success: true, result: selectResult.recordset, total: totalResult.recordset[0].NUM }
    } catch (err) {
        ctx.body = { success: false, err }
    }
}

// 查询购退单明细
const getVerifyPurchaseRetreatShop = async (ctx) => {
    try {
        const { ID } = ctx.request.body;
        let selectSql = ` SELECT TM.ID,TM.XH,TM.YPBM,TM.YPMC,TM.YPGG,TM.JLDW,TM.JXMC,TM.SCCJ,TM.PZWH,TM.SL,TM.SCPH,TM.DJ,TM.JE,TM.HWBH,TM.SCRQ,
        TM.YXQZ,TM.GTYY,TM.BZQK,TM.ZLZK,TM.FHJL,TM.CKBM,TM.CKMC,TM.MJSL,Y.YPLX,TM.ID_RKD,KC.KCSL,RM.SL AS RKSL,(RM.SL - ISNULL(TH.THSL,0)+ ISNULL(TM.SL,0)) AS SYSL,Y.SSXK 
        FROM THD_MX TM LEFT JOIN THD_ZB TZ ON TM.ID = TZ.ID LEFT JOIN BM_YP Y ON TM.YPBM = Y.BM LEFT JOIN ( SELECT ID,YPBM,SCPH,SUM(SL) AS SL FROM  RKD_MX GROUP BY ID,YPBM,SCPH) RM ON TM.ID_RKD =  RM.ID AND 
        TM.YPBM = RM.YPBM AND TM.SCPH = RM.SCPH LEFT JOIN KC_KCDTB KC ON TZ.BMBM = KC.BMBM AND TM.YPBM = KC.YPBM AND TM.CKBM = KC.CKBM AND 
        TM.SCPH = KC.SCPH LEFT JOIN (SELECT COUNT(TM.SL) AS THSL,TM.ID_RKD,TM.YPBM,TM.SCPH FROM THD_MX TM GROUP BY TM.ID_RKD,TM.YPBM,TM.SCPH) TH 
        ON TM.ID_RKD = TH.ID_RKD AND TM.YPBM = TH.YPBM AND TM.SCPH = TH.SCPH WHERE TM.ID = '${ID}' `;
        let selectResult = await sql.query(selectSql);

        ctx.body = { success: true, result: selectResult.recordset }
    } catch (err) {
        ctx.body = { success: false, err }
    }
}

// 把仓库编码、商品编码和生产批号相同合并成一条数据并把数量相加
const mergeShopList = (shopList) => {
    let mapShopList = shopList.map(item => {
        return {
            SL: item.SL,
            YPBM: item.YPBM,
            SCPH: item.SCPH,
            CKBM: item.CKBM
        }
    });

    let newShopList = [];
    mapShopList.forEach(item => {
        let isExist = newShopList.find(shop => {
            return item.YPBM === shop.YPBM && item.SCPH === shop.SCPH && item.CKBM === shop.CKBM
        });
        if (isExist) {
            isExist.SL = Number(item.SL) + Number(isExist.SL);
        } else {
            newShopList.push(item);
        }
    });
    return newShopList
}

// 审核购退单
const keepPurchaseRetreatOrder = async (ctx) => {
    try {
        const { orderList, shopList, SHR } = ctx.request.body;
        let actionSqlList = [];

        let mergedShopList = mergeShopList(shopList);
        for (let ind in mergedShopList) {
            let item = mergedShopList[ind];
            // 更新库存动态表
            let storeInfoSql = ` SELECT KCSL,KYSL,CBJJ FROM KC_KCDTB WHERE BMBM = '${orderList.BMBM}' AND CKBM = '${item.CKBM}' AND YPBM = '${item.YPBM}' AND SCPH = '${item.SCPH}' `;
            let storeInfoResult = await sql.query(storeInfoSql);
            let storeInfo = storeInfoResult.recordset[0];
            let sl = item.SL ? Number(item.SL) : 0;
            let cbjj = storeInfo.CBJJ ? Number(storeInfo.CBJJ) : 0;
            let oldKcsl = storeInfo.KCSL ? Number(storeInfo.KCSL) : 0;
            let oldKysl = storeInfo.KYSL ? Number(storeInfo.KYSL) : 0;
            let kcsl = oldKcsl - sl;
            let kysl = oldKysl - sl;
            let kcje = (kcsl * cbjj).toFixed(2);
            let updateKcSql = ` UPDATE KC_KCDTB SET KCSL = ${kcsl},KYSL = ${kysl},KCJE = ${kcje} WHERE BMBM = '${orderList.BMBM}' AND CKBM = '${item.CKBM}' AND YPBM = '${item.YPBM}' AND SCPH = '${item.SCPH}' `;
            actionSqlList.push(updateKcSql);
        }

        let zje = 0, zss = 0, zjs = 0, zsl = 0;
        for (let ind in shopList) {
            let item = shopList[ind];

            // 改变购退数量后，更新购退总表、明细和结算表
            let dj = Number(item.DJ ? item.DJ : 0);
            let gtsl = Number(item.SL ? item.SL : 0);
            let mjsl = Number(item.MJSL ? item.MJSL : 0);
            let je = dj * gtsl;
            let js = mjsl === 0 ? 0 : Math.floor(gtsl / mjsl);
            let ss = gtsl - (mjsl * js);
            zje += je;
            zss += ss;
            zjs += js;
            zsl += gtsl;
            let updateThdMxSql = ` UPDATE THD_MX SET SL = ${gtsl},JS = ${js},SS = ${ss},JE = ${je},HJ = ${je} WHERE ID= '${item.ID}' AND XH= '${item.XH}' `;
            actionSqlList.push(updateThdMxSql);
        }
        let selectWldwSql = ` SELECT * FROM BM_WLDW WHERE BM = '${orderList.DWBM}' `;
        let selectWldwResult = await sql.query(selectWldwSql);
        let selectWldw = selectWldwResult.recordset[0];
        let oldYinsye = selectWldw.YINSYE ? Number(selectWldw.YINSYE) : 0;
        let oldWlye = selectWldw.WLYE ? Number(selectWldw.WLYE) : 0;
        let yinsye = oldYinsye + zje;
        let wlye = oldWlye + zje;
        let updateWldwSql = ` UPDATE BM_WLDW SET YINSYE = ${yinsye},WLYE = ${wlye} WHERE BM = '${orderList.DWBM}' `;
        actionSqlList.push(updateWldwSql);
        //更新购退结算
        let updateThdJsSql = ` UPDATE THD_JS SET JSJE = ${zje}  WHERE ID = '${orderList.ID}'`;
        actionSqlList.push(updateThdJsSql);
        // 改变退货单的审核状态
        let updateThdZbSql = ` UPDATE THD_ZB SET ZSL = ${zsl},ZJS = ${zjs},ZSS = ${zss},ZJE = ${zje},ZHJ = ${zje},YJS = ${zje},WJS = ${zje},SH='T',SHR = '${SHR}' WHERE ID = '${orderList.ID}' `;
        actionSqlList.push(updateThdZbSql);

        let lastSql = `SET XACT_ABORT ON Begin Tran ${actionSqlList.join(' ')}  Commit Tran `;
        let lastResult = await sql.query(lastSql);
        if (lastResult.rowsAffected[0] > 0) {
            ctx.body = { success: true }
        } else {
            ctx.body = { success: false }
        }
    } catch (err) {
        ctx.body = { success: false, err }
    }
}

// 保存购退的物流信息
const keepPurchaseRetreatLogisticsInfo = async (ctx) => {
    try {
        const { id, YFJS, WLGS, WLDH, BZ } = ctx.request.body;
        let updateSql = ` update THD_ZB SET YFJS='${YFJS}',WLGS='${WLGS}',WLDH='${WLDH}',CLYJ='${BZ}' WHERE ID = '${id}' `;
        let result = await sql.query(updateSql);
        if (result.rowsAffected[0] > 0) {
            ctx.body = { code: 200, message: '保存成功', data: { result: result.recordset } }
        } else {
            ctx.body = { code: 400, message: '保存失败' }
        }

    } catch (err) {
        ctx.body = { code: 500, err }
    }
}


// 查询要导出的订单详情
const getExportPurchaseInfo = async (ctx) => {
    try {

        const { THID, DWBM, BMBM, isSh, YPBM, times } = ctx.request.body;
        let selectSql = ` SELECT TZ.RQ,TZ.DWMC,TM.YPBM,TM.YPMC,TM.YPGG,TM.PZWH,TM.SCCJ,TM.JLDW,TM.SL,TM.SCPH  FROM THD_MX TM LEFT JOIN 
        THD_ZB TZ ON TM.ID = TZ.ID LEFT JOIN BM_WLDW DW ON TZ.DWBM = DW.BM  LEFT JOIN BM_YP Y ON TM.YPBM = Y.BM WHERE  1=1  `;
        let searchSqlList = [];
        if (DWBM) {
            searchSqlList.push(` AND (DW.BM LIKE '%${DWBM}%' OR DW.MC LIKE '%${DWBM}%' OR DW.JP LIKE '%${DWBM}%' ) `);
        }

        if (YPBM) {
            searchSqlList.push(` AND (Y.BM LIKE '%${YPBM}%' OR Y.MC LIKE '%${YPBM}%' OR Y.JP LIKE '%${YPBM}%' ) `);
        }
        if (times[0]) {
            searchSqlList.push(` AND TZ.RQ >= '${times[0]} 00:00:00' AND TZ.RQ <= '${times[1]} 23:59:59' `);
        }
        if (BMBM) {
            searchSqlList.push(` AND TZ.BMBM = '${BMBM}' `);
        }
        if (isSh) {
            searchSqlList.push(` AND TZ.SH = '${isSh}' `);
        }
        if (THID) {
            searchSqlList.push(` AND TZ.ID = '${THID}' `);
        }
        selectSql += searchSqlList.join(' ');
        let selectResult = await sql.query(selectSql);

        if (selectResult.rowsAffected[0] > 0) {
            ctx.body = { code: 200, message: '获取数据成功', data: { result: selectResult.recordset } }
        } else {
            ctx.body = { code: 400, message: '获取数据失败' }
        }

    } catch (err) {
        ctx.body = { code: 500, message: err }
    }
}


module.exports = {
    'POST /getVerifyPurchaseRetreatOrder': getVerifyPurchaseRetreatOrder,
    'POST /getVerifyPurchaseRetreatShop': getVerifyPurchaseRetreatShop,
    'POST /keepPurchaseRetreatLogisticsInfo': keepPurchaseRetreatLogisticsInfo,
    'POST /getExportPurchaseInfo': getExportPurchaseInfo,
    'POST /keepPurchaseRetreatOrder': keepPurchaseRetreatOrder

}